package cn.virens.util.poi.common;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.LocaleUtil;

import cn.virens.util.poi.exception.ReadExcelException;

public class ReadCellUtil {
	/**
	 * 获取字符串
	 * @param row
	 * @param i
	 * @param nan
	 * @return
	 * @throws ReadExcelException
	 */
	public static String getString(Row row, int i) {
		Cell cell = row.getCell(i);
		if (cell == null) return null;
		CellType type = cell.getCellTypeEnum();
		if (CellType.ERROR.equals(type)) {
			return "";
		} else if (CellType.NUMERIC.equals(type)) {
			if (DateUtil.isCellDateFormatted(cell)) {
				DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", LocaleUtil.getUserLocale());
				sdf.setTimeZone(LocaleUtil.getUserTimeZone());
				return sdf.format(cell.getDateCellValue());
			} else {
				return Double.toString(cell.getNumericCellValue());
			}
		} else {
			return cell.toString();
		}
	}

	/**
	 * 获取Integer类型数据
	 * @param row
	 * @param i
	 * @return
	 * @throws ReadExcelException
	 */
	public static Integer getInteger(Row row, int i) throws ReadExcelException {
		String value = getString(row, i);
		try {
			return StringUtils.isEmpty(value) ? null : Integer.valueOf(value);
		} catch (Exception e) {
			throw new ReadExcelException("该字段不是有效的数字", row.getRowNum(), i, value);
		}
	}

	/**
	 * 获取浮点类型数字
	 * @param row
	 * @param i
	 * @param nan
	 * @return
	 * @throws ReadExcelException
	 */
	public static Float getFloat(Row row, int i) throws ReadExcelException {
		String value = getString(row, i);
		try {
			return StringUtils.isEmpty(value) ? null : Float.valueOf(value);
		} catch (Exception e) {
			throw new ReadExcelException("该字段不是有效的数字", row.getRowNum(), i, value);
		}
	}

	/**
	 * 获取Boolean类型
	 * @param row
	 * @param i
	 * @param nan
	 * @return
	 * @throws ReadExcelException
	 */
	public static Boolean getBoolean(Row row, int i) {
		String value = getString(row, i);
		if (value == null) {
			return null;
		} else if ("true".equalsIgnoreCase(value) || "on".equals(value) || "是".equals(value) || "1".equals(value)) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 获取时间类型
	 * @param row
	 * @param i
	 * @param nan
	 * @return
	 * @throws ReadExcelException
	 */
	public static Date getDate(Row row, int i) throws ReadExcelException {
		Cell cell = row.getCell(i);
		if (cell == null) return null;
		CellType type = cell.getCellTypeEnum();
		if (CellType.BLANK.equals(type) || CellType.ERROR.equals(type)) {
			return null;
		} else if (CellType.NUMERIC.equals(type)) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			} else {
				return format(cell.toString());
			}
		} else {
			return format(cell.toString());
		}
	}

	protected static Date format(String string) {
		try {
			string = string.replaceAll("[\\t\\n\\r]", "").replaceAll("[/|年|月|日|-]", ".");
			int i = string.lastIndexOf(".");
			if (i > -1) {
				string = string.substring(0, i);
			}
			if (string.length() <= 4) {
				return new SimpleDateFormat("yyyy").parse(string);
			} else if (string.length() <= 7) {
				return new SimpleDateFormat("yyyy.MM").parse(string);
			} else {
				return new SimpleDateFormat("yyyy.MM.dd").parse(string);
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return null;
	}
}
